package DBLayer;
import ModelLayer.OrderDetail;
import java.sql.*;
import java.util.ArrayList;

public class DBOrderDetail {
    // <editor-fold defaultstate="collapsed" desc="Attributes & Constructors">
    private Connection conn;

    public DBOrderDetail() {
        conn = SQLConnector.getInstance().getConnection();
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL SELECT Methods">
    public OrderDetail getOrderDetailByID(int id) {
        return selectSingle("SELECT `id_order_detail`, `id_order`, `product_id`, `product_attribute_id`, `product_name`, `product_quantity`, `product_quantity_in_stock`, `product_quantity_refunded`, `product_quantity_return`, `product_quantity_reinjected`, `product_price`, `product_quantity_discount`, `product_ean13`, `product_reference`, `product_supplier_reference`, `product_weight`, `tax_name`, `tax_rate`, `ecotax`, `download_hash`, `download_nb`, `download_deadline` FROM `ps_order_detail` WHERE `id_order_detail` = " + id + " LIMIT 0, 1;");
    }

    public ArrayList<OrderDetail> getOrderDetailList() {
        return selectMultiple("SELECT `id_order_detail`, `id_order`, `product_id`, `product_attribute_id`, `product_name`, `product_quantity`, `product_quantity_in_stock`, `product_quantity_refunded`, `product_quantity_return`, `product_quantity_reinjected`, `product_price`, `product_quantity_discount`, `product_ean13`, `product_reference`, `product_supplier_reference`, `product_weight`, `tax_name`, `tax_rate`, `ecotax`, `download_hash`, `download_nb`, `download_deadline` FROM `ps_order_detail` ORDER BY `id_order_detail` ASC;");
    }

    public ArrayList<OrderDetail> getOrderDetailListByOrderID(int id) {
        return selectMultiple("SELECT `id_order_detail`, `id_order`, `product_id`, `product_attribute_id`, `product_name`, `product_quantity`, `product_quantity_in_stock`, `product_quantity_refunded`, `product_quantity_return`, `product_quantity_reinjected`, `product_price`, `product_quantity_discount`, `product_ean13`, `product_reference`, `product_supplier_reference`, `product_weight`, `tax_name`, `tax_rate`, `ecotax`, `download_hash`, `download_nb`, `download_deadline` FROM `ps_order_detail` WHERE `id_order` = " + id + " ORDER BY `id_order_detail` ASC;");
    }

    private OrderDetail selectSingle(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        OrderDetail orderDetail = new OrderDetail();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            if (result.next()) {
                orderDetail = buildOrderDetail(result);
            }
            System.out.println("Fetching OrderDetail Object");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return orderDetail;
    }

    private ArrayList<OrderDetail> selectMultiple(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<OrderDetail> orderDetails = new ArrayList<OrderDetail>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                OrderDetail orderDetail = new OrderDetail();
                orderDetail = buildOrderDetail(result);
                orderDetails.add(orderDetail);
            }
            System.out.println("Fetching OrderDetail List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return orderDetails;
    }

    private OrderDetail buildOrderDetail(ResultSet result) {
        OrderDetail orderDetail = new OrderDetail();
        try {
            orderDetail.setIDOrderDetail(result.getInt("id_order_detail"));
            orderDetail.setIDOrder(result.getInt("id_order"));
            orderDetail.setIDProduct(result.getInt("product_id"));
            orderDetail.setIDProductAttribute(result.getInt("product_attribute_id"));
            orderDetail.setProductName(result.getString("product_name"));
            orderDetail.setProductQuantity(result.getInt("product_quantity"));
            orderDetail.setProductQuantityInStock(result.getInt("product_quantity_in_stock"));
            orderDetail.setProductQuantityRefunded(result.getInt("product_quantity_refunded"));
            orderDetail.setProductQuantityReturn(result.getInt("product_quantity_return"));
            orderDetail.setProductQuantityReinjected(result.getInt("product_quantity_reinjected"));
            orderDetail.setProductPrice(result.getFloat("product_price"));
            orderDetail.setProductQuantityDiscount(result.getFloat("product_quantity_discount"));
            orderDetail.setProductEan13(result.getString("product_ean13"));
            orderDetail.setProductReference(result.getString("product_reference"));
            orderDetail.setProductSupplierReference(result.getString("product_supplier_reference"));
            orderDetail.setProductWeight(result.getFloat("product_weight"));
            orderDetail.setTaxName(result.getString("tax_name"));
            orderDetail.setTaxRate(result.getFloat("tax_rate"));
            orderDetail.setEcotax(result.getFloat("ecotax"));
            orderDetail.setDownloadHash(result.getString("download_hash"));
            orderDetail.setDownloadNB(result.getInt("download_nb"));
            orderDetail.setDownloadDeadline(result.getInt("download_deadline"));

        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
        return orderDetail;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL INSERT, UPDATE & DELETE Methods">
    public int addOrderDetail(OrderDetail orderDetail) {
        int returnID = 0;
        Statement stmt = null;
        ResultSet result = null;

        String query = "INSERT INTO `ps_order_detail` (`id_order`, `product_id`, `product_attribute_id`, `product_name`, `product_quantity`, `product_quantity_in_stock`, `product_quantity_refunded`, `product_quantity_return`, `product_quantity_reinjected`, `product_price`, `product_quantity_discount`, `product_ean13`, `product_reference`, `product_supplier_reference`, `product_weight`, `tax_name`, `tax_rate`, `ecotax`, `download_hash`, `download_nb`, `download_deadline`) "
                + "VALUES (" + orderDetail.getIDOrder() + ", " + orderDetail.getIDProduct() + ", " + orderDetail.getIDProductAttribute() + ", '" + orderDetail.getProductName() + "', " + orderDetail.getProductQuantity() + ", " + orderDetail.getProductQuantityInStock() + ", " + orderDetail.getProductQuantityRefunded() + ", " + orderDetail.getProductQuantityReturn() + ", " + orderDetail.getProductQuantityReinjected() + ", " + orderDetail.getProductPrice() + ", " + orderDetail.getProductQuantityDiscount() + ", '" + orderDetail.getProductEan13() + "', '" + orderDetail.getProductReference() + "', '" + orderDetail.getProductSupplierReference() + "', " + orderDetail.getProductWeight() + ", '" + orderDetail.getTaxName() + "', " + orderDetail.getTaxRate() + ", " + orderDetail.getEcotax() + ", '" + orderDetail.getDownloadHash() + "', " + orderDetail.getDownloadNB() + ", " + orderDetail.getDownloadDeadline() + ")";
        System.out.println(query);
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            result = stmt.getGeneratedKeys();
            if (result.next()) {
                returnID = result.getInt(1);
            }
            System.out.println("Added OrderDetail ID " + returnID);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public int updateOrderDetail(OrderDetail orderDetail, int id) {
        int returnID = 0;
        Statement stmt = null;
        String query = "UPDATE `ps_order_detail` SET "
                + "`id_order_detail                 = "  + orderDetail.getIDOrderDetail() + ", "
                + "`id_order`                       = "  + orderDetail.getIDOrder() + ", "
                + "`product_id`                     = "  + orderDetail.getIDProduct() + ", "
                + "`product_attribute_id`           = "  + orderDetail.getIDProductAttribute() + ", "
                + "`product_name                    = '" + orderDetail.getProductName() + "', "
                + "`product_quantity`               = "  + orderDetail.getProductQuantity() + ", "
                + "`product_quantity_in_stock`      = "  + orderDetail.getProductQuantityInStock() + ", "
                + "`product_quantity_refunded`      = "  + orderDetail.getProductQuantityRefunded() + ", "
                + "`product_quantity_return`        = "  + orderDetail.getProductQuantityReturn() + ", "
                + "`product_quantity_reinjected`    = "  + orderDetail.getProductQuantityReinjected() + ", "
                + "`product_price`                  = "  + orderDetail.getProductPrice() + ", "
                + "`product_quantity_discount`      = "  + orderDetail.getProductQuantityDiscount() + ", "
                + "`product_ean13`                  = '" + orderDetail.getProductEan13() + "', "
                + "`product_reference`              = '" + orderDetail.getProductReference() + "', "
                + "`product_supplier_reference`     = '" + orderDetail.getProductSupplierReference() + "', "
                + "`product_weight`                 = "  + orderDetail.getProductWeight() + ", "
                + "`tax_name`                       = '" + orderDetail.getTaxName() + "', "
                + "`tax_rate`                       = "  + orderDetail.getTaxRate() + ", "
                + "`ecotax`                         = "  + orderDetail.getEcotax() + ", "
                + "`download_hash`                  = "  + orderDetail.getDownloadHash() + ", "
                + "`download_nb`                    = "  + orderDetail.getDownloadNB() + ", "
                + "`download_deadline`              = "  + orderDetail.getDownloadDeadline() + " "
                + "WHERE `id_order_detail` = " + id + " LIMIT 1;";

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            returnID = orderDetail.getIDOrderDetail();
            System.out.println("Updated OrderDetail ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public void deleteOrderDetail(int id) {
        Statement stmt = null;

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM `ps_order_detail` WHERE `id_order_detail` = " + id + " LIMIT 1;");
            System.out.println("Deleted OrderDetail ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
    }
    // </editor-fold>
}
